package com.yhl.sftp;


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @description: 每月5号22：00 将业务信息查询里的全量业务数据导出，放到对方提供的文件服务器上。
 */
@Component
public class BusinessDataExportImpl {
    // @Autowired
    // private PdBusiInstanceInfoDao pdBusiInstanceInfoDao;
    private static Log log = LogFactory.getLog(BusinessDataExportImpl.class);

    // 创建表头
    private static final String[] headers = {
            "业务信息ID", "一站式业务信息ID", "产品名称", "Bound类型", "发起运营商", "发起方", "电路代号",
            "发起方客户经理", "客户经理电话", "客户经理EMAIL", "CUST_ID", "客户名称", "客户联系人",
            "客户联系人电话", "客户联系人电话", "售前经理", "A端或CE端城市", "A端或CE端运营商",
            "A端或CE端客户名称", "A端或CE端客户联系人", "Z端或PE端城市", "Z端或PE端运营商",
            "Z端或PE端客户名称", "Z端或PE端客户联系人", "起租时间", "止租时间", "状态", "带宽"
    };
    private static final String[] columns = {
            "BUSI_ID", "OSS_BUSI_ID", "PROD_NAME", "BOUNDTYPE", "OPERATOR", "SOURCE", "BUSI_PHY_NUMBER",
            "CUST_MNG_NAME", "CUST_MNG_TEL", "CUST_MNG_EMAIL", "CUST_ID", "CUST_NAME", "CUST_CONTACT",
            "CONTACT_TEL", "CONTACT_EMAIL", "SALE_MANAGE_NAME", "ACITY", "AOPERATOR",
            "ACUSTNAME", "ACUSTCONTACT", "ZCITY", "ZOPERATOR",
            "ZCUSTNAME", "ZCUSTCONTACT", "START_RENT_DATE", "STOP_RENT_DATE", "STATE", "SPEEDNO"
    };
    // @Scheduled(cron = "0 * * * * ?")
    public void doTask() throws Exception {
        // 1.获取数据
        HashMap<String, Object> qryMap = new HashMap<String, Object>();
        // List<Map> businessDataList = pdBusiInstanceInfoDao.qryAllBusinessData(qryMap);
        List<Map> businessDataList = new ArrayList<Map>();
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("BUSI_ID", "B00000256");
        map.put("OSS_BUSI_ID", "6258958");
        map.put("PROD_NAME", "1000");
        map.put("BOUNDTYPE", "数字电路");
        map.put("OPERATOR", "IB");
        businessDataList.add(map);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        String curDate = new SimpleDateFormat("yyyyMM")
                .format(calendar.getTime());
        String fileName = curDate + "_Bizinfo.xlsx";
        String targetFile = curDate + "_Bizinfo.txt";
        String sheetName = "业务信息";
        // 2.组装excel
        String file = createExcel(headers, columns, fileName, sheetName, businessDataList);
        System.out.println(file);

        // 3.上传文件到对方文件服务器
        uploadFile(file, targetFile);

    }

    public static String createExcel(String[] headers, String[] columns, String fileName, String sheetName, List<Map> dataList){
        // 创建一个新的工作簿 SXSSFWorkbook流式处理
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 创建一个新的工作表
        Sheet sheet = workbook.createSheet(sheetName);
        // 创建表头行
        Row headerRow = sheet.createRow(0);
        // 创建一个字体样式并设置为加粗
        Font headerFont = workbook.createFont();
        headerFont.setFontName("Arial");
        headerFont.setBold(true);
        // 创建一个单元格样式并将字体样式应用到该单元格样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);
        headerRow.setRowStyle(headerCellStyle);
        // 填充表头
        Cell cell;
        for (int i = 0; i < headers.length; i++) {
            cell = headerRow.createCell(i);
            cell.setCellStyle(headerCellStyle);
            cell.setCellValue(headers[i]);
        }
        // 自动调整列宽
        // for (int i = 0; i < headers.length; i++) {
        //     sheet.autoSizeColumn(i);
        // }
        // 填充数据
        int rowNum = 0;
        Row row;
        for (Map map : dataList) {
            row = sheet.createRow(++rowNum);
            for (int j = 0; j < columns.length; j++) {
                cell = row.createCell(j);
                // cell.setCellStyle();
                cell.setCellValue(MapUtils.getString(map, columns[j], ""));
            }
        }

        // 将工作簿写入文件
        String saveFilePath = "D:/";
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(saveFilePath + fileName);
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            // 关闭资源
            try {
                if (fileOut != null) {
                    fileOut.close();
                }
            } catch (IOException ex) {
                ex.printStackTrace();
            }

        }
        return fileName;
    }

    public boolean uploadFile(String fileName, String targetFile) throws Exception {
        String localPath = "D:/";
        String ftpHost = "137.0.16.167";
        String ftpUserName = "yezhi";
        String ftpPassword = "Yez#hi#2024";
        int ftpPort = 22;
        String remotePath = "/home/yezhi/trafficCircuit";

        // FtpJSchUtil sftpUtil;
        // sftpUtil = FtpJSchUtil.getConnect(ftpHost,ftpPort,ftpUserName,ftpPassword);
        // String uploadRet = sftpUtil.uploadNew(remotePath, localPath, fileName, targetFile);
        // return uploadRet != null;


        SftpUtil sftpUtil = new SftpUtil(ftpUserName, ftpPassword, ftpHost, ftpPort);
        sftpUtil.login();
        sftpUtil.upload(remotePath, localPath + fileName);
        return true;

        // FtpUtil ftp = new FtpUtil();
        // ftp.setPassiveFlag(true);
        // String localPath = prop.getProperty("businessDataFile.save");
        // String IP = prop.getProperty("businessData.ftp.IP");
        // String userName = prop.getProperty("businessData.ftp.username");
        // String passWord = prop.getProperty("businessData.ftp.password");
        // int port = Integer.parseInt(prop.getProperty("businessData.ftp.port"));
        // String remotePath = prop.getProperty("businessData.ftp.upload");
        // boolean f = false;
        // return f = ftp.ftpUploadEncrypt(IP, port, userName, passWord, localPath, remotePath, fileName, targetFile);
    }

    public static void main(String[] args) throws Exception {
        BusinessDataExportImpl businessDataExport = new BusinessDataExportImpl();
        businessDataExport.doTask();
    }


}

